//=====================================================================  	
//
//  File:       Main.cs
//
//  Summary:    This file contains the builds a package programmatically. It adds a Data flow task 
//              to the Executables collection, and adds three Data Flow components
//              (OLEDB Source, Sort, and Flat File Destination) 
//              to the data flow ComponentMetaDataCollection.
//
//  Date:       06/09/2005
//
//---------------------------------------------------------------------
//  This file is part of the Microsoft SQL Server Code Samples.
//  Copyright (C) Microsoft Corporation.  All rights reserved.
//
//  This source code is intended only as a supplement to Microsoft
//  Development Tools and/or on-line documentation.  See these other
//  materials for detailed information regarding Microsoft code samples.
//
//  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
//  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
//  THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.
//=====================================================================                         

using System;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;              // Managed Runtime namespace           [Microsoft.SqlServer.ManagedDTS.dll]
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;     // Pipeline Primary Interop Assembly   [Microsoft.SqlServer.DTSPipelineWrap.dll]
using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace Microsoft.Samples.SqlServer.SSIS
{
    public class OleDBToFlatFile
    {
        const string PACKAGEFILENAME = @"\SampleRuntimePackage.dtsx";
        const string DATAFILENAME = @"\SampleRuntimeData.txt";
        const string LOGFILENAME = @"\SampleRuntimeLog.log";

        private Package package;                                // package object
        private MainPipe dataFlow;                              // dataFlow task object
        private IDTSComponentMetaData100 oledbSource;            // OLEDB dataFlow component object
        private IDTSComponentMetaData100 sort;                   // Sort transform dataFlow component object
        private IDTSComponentMetaData100 flatfileDestination;    // FlatFile dataFlow component object
        private PackageEvents packageEvents;            // class that implements the Package events interface IDTSEvents100
        private ComponentEvents pipelineEvents;         // class that implements the component events interface IDTSComponentEvents100

        #region Constructor
        public OleDBToFlatFile(string sortColumnName)
        {
            if (string.IsNullOrEmpty(sortColumnName))
            {
                sortColumnName = "Name";
            }

            string currentDirectory = Directory.GetCurrentDirectory();

            // Delete the log file, destination, and package files.
            File.Delete(currentDirectory + LOGFILENAME);
            File.Delete(currentDirectory + DATAFILENAME);
            File.Delete(currentDirectory + PACKAGEFILENAME);

            this.package = CreatePackage("SSISPackage", "The package for the SSIS CreatePackage sample");
            this.package.Variables.Add("SortColumn", false, "", sortColumnName);

            // Events.
            this.packageEvents = new PackageEvents();
            this.pipelineEvents = new ComponentEvents();

            // Add the Runtime objects.
            this.AddLogging(currentDirectory + LOGFILENAME);
            this.AddConnectionManagers(currentDirectory);
            this.AddDataFlowTask();

            // Add the DataFlow components.
            this.AddOLEDBSource();
            this.AddSort();
            this.AddFlatFileDestination();

            // Validate the layout of the package.
            DTSExecResult status = this.package.Validate(null, null, this.packageEvents, null);
            System.Console.WriteLine("Validation result: " + status);

            // Save the package
            Application a = new Application();
            a.SaveToXml(currentDirectory + PACKAGEFILENAME, this.package, this.packageEvents);
            Console.WriteLine("Package saved to " + currentDirectory + PACKAGEFILENAME);

            // If the package validated successfully, then execute it.
            if (status == DTSExecResult.Success)
            {
                Console.WriteLine("Beginning package execution.");

                // Execute the package
                DTSExecResult result = this.package.Execute(null, null, this.packageEvents, null, null);
                Console.WriteLine("Execution result: " + result);
            }

            if (File.Exists(currentDirectory + DATAFILENAME))
            {
                Console.WriteLine("Sample data saved to " + currentDirectory + DATAFILENAME);
            }
        }
        #endregion

        #region CreatePackage
        private static Package CreatePackage(string Name, string Description)
        {
            Package p = new Package();
            p.PackageType = DTSPackageType.DTSDesigner100;
            p.Name = Name;
            p.Description = Description;
            p.CreatorComputerName = System.Environment.MachineName;
            p.CreatorName = System.Environment.UserName;

            return p;
        }
        #endregion

        #region AddLogging
        /// <summary>
        /// Enable package level logging.
        /// </summary>
        private void AddLogging(string Path)
        {
            try
            {
                this.package.LoggingMode = DTSLoggingMode.Enabled;

                // Add a file connection manager for the text log provider.
                ConnectionManager cm = this.package.Connections.Add("FILE");

                cm.ConnectionString = Path;
                cm.Name = "FileLogProviderConnection";

                // Add a LogProvider.
                LogProvider provider = this.package.LogProviders.Add("DTS.LogProviderTextFile");

                provider.ConfigString = cm.Name;
                this.package.LoggingOptions.SelectedLogProviders.Add(provider);
            }
            catch (System.NullReferenceException nre)
            {
                System.Diagnostics.Debug.WriteLine(nre.StackTrace);
            }
        }
        #endregion

        #region AddConnectionManagers
        /// <summary>
        /// Adds the OLEDB and FlatFile connection managers to the package.
        /// </summary>
        private void AddConnectionManagers(string DestinationDataDirectory)
        {
            // Add the OLEDB connection manager.
            ConnectionManager adventureWorks = this.package.Connections.Add("OLEDB");

            // Set stock properties.
            adventureWorks.Name = "OLEDBConnection";
            adventureWorks.ConnectionString = @"Provider=SQLNCLI10;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=(local);Auto Translate=False;";

            // Add the Destination connection manager.
            ConnectionManager cmflatFile = this.package.Connections.Add("FLATFILE");

            // Set the stock properties.
            cmflatFile.Properties["ConnectionString"].SetValue(cmflatFile, DestinationDataDirectory + DATAFILENAME);
            cmflatFile.Properties["Format"].SetValue(cmflatFile, "Delimited");
            cmflatFile.Properties["DataRowsToSkip"].SetValue(cmflatFile, 0);
            cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, false);
            cmflatFile.Properties["Name"].SetValue(cmflatFile, "FlatFileConnection");
            cmflatFile.Properties["RowDelimiter"].SetValue(cmflatFile, "\r\n");
            cmflatFile.Properties["TextQualifier"].SetValue(cmflatFile, "\"");
        }
        #endregion

        #region AddDataFlowTask
        /// <summary>
        /// Adds a DataFlow task to the Executables collection of the package.
        /// Retrieves the MainPipe object from the TaskHost and stores it in 
        /// the dataFlow member variable
        /// </summary>
        private void AddDataFlowTask()
        {
            TaskHost th = this.package.Executables.Add("STOCK:PipelineTask") as TaskHost;
            th.Name = "DataFlow";
            th.Description = "The DataFlow task in the DTSAuto sample.";

            this.dataFlow = th.InnerObject as MainPipe;
            this.dataFlow.Events = this.pipelineEvents as wrap.IDTSComponentEvents100;
        }
        #endregion

        #region AddOLEDBSource
        /// <summary>
        /// Adds the OLEDB Data Source component to the DataFlow task.
        /// Creates an instance of the component.
        /// Sets the runtime connection manager.
        /// Sets two custom properties; the SqlCommand, and ValidateColumnMetaData.
        /// Acquires the connection and Reinitializes the metadata.
        /// </summary>
        private void AddOLEDBSource()
        {
            this.oledbSource = this.dataFlow.ComponentMetaDataCollection.New();

            // Set stock properties.
            this.oledbSource.ComponentClassID = "DTSAdapter.OLEDBSource";
            this.oledbSource.Name = "OLEDBSource";
            this.oledbSource.Description = "Source data in the dataFlow";

            CManagedComponentWrapper instance = this.oledbSource.Instantiate();
            instance.ProvideComponentProperties();

            // Associate the runtime connection manager
            //  The connection manager association will fail if called before ProvideComponentProperties
            this.oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID
                = this.package.Connections["OLEDBConnection"].ID;
            this.oledbSource.RuntimeConnectionCollection[0].ConnectionManager
                = DtsConvert.GetExtendedInterface(this.package.Connections["OLEDBConnection"]);

            // set custom component properties
            instance.SetComponentProperty("OpenRowset", "[Production].[Product]");
            instance.SetComponentProperty("AccessMode", 0);

            // Acquire Connections and reinitialize the component
            instance.AcquireConnections(null);
            instance.ReinitializeMetaData();
            instance.ReleaseConnections();
        }
        #endregion

        #region AddSort
        /// <summary>
        /// Adds the DTSTransform.Sort to the DataFlow task.
        /// </summary>
        private void AddSort()
        {
            // Add the component to the DataFlow task.
            this.sort = this.dataFlow.ComponentMetaDataCollection.New();

            // Set component's stock properties.
            this.sort.ComponentClassID = "DTSTransform.Sort";
            this.sort.Name = "SortTransform";
            this.sort.Description = "Sort component";

            CManagedComponentWrapper instance = this.sort.Instantiate();
            instance.ProvideComponentProperties();

            // Attach path between the OLEDB source components Output, and the Sort Component's Input.
            this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(
                this.oledbSource.OutputCollection[0], this.sort.InputCollection[0]);

            IDTSVirtualInput100 vInput = this.sort.InputCollection[0].GetVirtualInput();

            foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
            {
                IDTSInputColumn100 col = instance.SetUsageType(this.sort.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

                if (col.Name == this.package.Variables["SortColumn"].Value.ToString())
                {
                    if (col.CustomPropertyCollection.Count > 0)
                    {
                        instance.SetInputColumnProperty(this.sort.InputCollection[0].ID, col.ID, "NewSortKeyPosition", 1);
                    }
                }
            }
        }

        #endregion

        #region AddFlatFileDestination
        /// <summary>
        /// 
        /// </summary>
        private void AddFlatFileDestination()
        {
            // Add the component to the dataFlow metadata collection
            this.flatfileDestination = this.dataFlow.ComponentMetaDataCollection.New();

            // Set the common properties
            this.flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";
            this.flatfileDestination.Name = "FlatFileDestination";
            this.flatfileDestination.Description = "Flat file destination";

            // Create an instance of the component
            CManagedComponentWrapper inst = this.flatfileDestination.Instantiate();
            inst.ProvideComponentProperties();

            // Associate the runtime ConnectionManager with the component
            this.flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID
                = this.package.Connections["FlatFileConnection"].ID;
            this.flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager
                = DtsConvert.GetExtendedInterface(
                this.package.Connections["FlatFileConnection"]);

            // Map a path between the Sort transformation component to the FlatFileDestination
            this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(
                this.sort.OutputCollection[0], this.flatfileDestination.InputCollection[0]);

            // Add columns to the FlatFileConnectionManager
            this.AddColumnsToFlatFileConnection();

            // Acquire the connection, reinitialize the metadata, 
            // map the columns, then release the connection.
            inst.AcquireConnections(null);
            inst.ReinitializeMetaData();
            this.MapFlatFileDestinationColumns();
            inst.ReleaseConnections();
        }
        #endregion

        #region MapFlatFileDestination Columns
        private void MapFlatFileDestinationColumns()
        {
            CManagedComponentWrapper wrp = this.flatfileDestination.Instantiate();

            IDTSVirtualInput100 vInput = this.flatfileDestination.InputCollection[0].GetVirtualInput();
            foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
            {
                wrp.SetUsageType(this.flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }

            // For each column in the input collection
            // find the corresponding external metadata column.
            foreach (IDTSInputColumn100 col in this.flatfileDestination.InputCollection[0].InputColumnCollection)
            {
                IDTSExternalMetadataColumn100 exCol = this.flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];
                wrp.MapInputColumn(this.flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);
            }
        }
        #endregion

        #region AddColumnsToFlatFileConnection
        private void AddColumnsToFlatFileConnection()
        {
            wrap.IDTSConnectionManagerFlatFile100 ff = null;
            foreach (ConnectionManager cm in this.package.Connections)
            {
                if (cm.Name == "FlatFileConnection")
                {
                    ff = cm.InnerObject as wrap.IDTSConnectionManagerFlatFile100;
                    DtsConvert.GetExtendedInterface(cm);
                }
            }

            // if the connection manager is null here, then we have a problem
            if (ff != null)
            {
                // Get the upstream columns
                IDTSVirtualInputColumnCollection100 vColumns = this.flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;

                for (int cols = 0; cols < vColumns.Count; cols++)
                {
                    wrap.IDTSConnectionManagerFlatFileColumn100 col = ff.Columns.Add();

                    // If this is the last column, set the delimiter to CRLF.
                    // Otherwise keep the delimiter as ",".
                    if (cols == vColumns.Count - 1)
                    {
                        col.ColumnDelimiter = "\r\n";
                    }
                    else
                    {
                        col.ColumnDelimiter = @",";
                    }

                    col.ColumnType = "Delimited";
                    col.DataType = vColumns[cols].DataType;
                    col.DataPrecision = vColumns[cols].Precision;
                    col.DataScale = vColumns[cols].Scale;
                    wrap.IDTSName100 name = col as wrap.IDTSName100;
                    name.Name = vColumns[cols].Name;
                }
            }
        }
        #endregion
    }
}
